﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Reflection;

/// <summary>
/// Summary description for DatabaseClass
/// </summary>
public class DatabaseClass
{
    public DatabaseClass()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    DbCommand cm = null;
    DataSet ds = null;
    SqlDatabase db = null; 
    string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionStringDev"].ToString();


    public IDataReader SelectDataReader_ProcName(string ProcName, List<DatabaseFields> parameters = null)
    {
        try
        {
            db = new SqlDatabase(conStr);
            using (cm = db.GetStoredProcCommand(ProcName))
            {
                if (parameters != null)
                    foreach (DatabaseFields item in parameters)
                        db.AddInParameter(cm, item.FieldName, item.FieldType, item.FieldValue);

                return db.ExecuteReader(cm);
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        return null;
    }
 
    public bool ExecProc(string ProcName, List<DatabaseFields> parameters=null)
    {
        try
        {
            db = new SqlDatabase(conStr);
            using (cm = db.GetStoredProcCommand(ProcName))
            {
                if(parameters != null)
                foreach(DatabaseFields item in parameters)
                    db.AddInParameter(cm, item.FieldName, item.FieldType, item.FieldValue);
                
                if (db.ExecuteNonQuery(cm) > 0)
                    return true;
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        return false;
    }


    public DataSet SelectDataset_ProcName(string ProcName, List<DatabaseFields> parameters = null)
    {
        using (ds = new DataSet())
        {
            try
            {
                db = new SqlDatabase(conStr);
                using (cm = db.GetStoredProcCommand(ProcName))
                {
                    if (parameters != null)
                        foreach (DatabaseFields item in parameters)
                            db.AddInParameter(cm, item.FieldName, item.FieldType, item.FieldValue);
                    db.LoadDataSet(cm, ds, "tbl");
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
        return ds;
    }
}


public struct DatabaseFields
{
    public string FieldName { get; set; }
    public object FieldValue { get; set; }
    public DbType FieldType { get; set; }
}
